iT邦幫忙

7

【SQL分享】如何在oracle找到前12個月連續四個月的平均消費金額

oracle sql
暐翰 2018-09-01 09:15:44 ‧ 6195 瀏覽

  • 分享至 

  • xImage
    •  

表格結構

月份 消費金額
01/04/17 10000
01/05/17 10000
01/06/17 10000
01/07/17 13000
01/08/17 11000
01/09/17 12000
01/10/17 10000
01/11/17 12000

期望結果

日期區間 平均消費金額
04/17 - 07/17 10750
05/17 - 08/17 11250
06/17 - 09/17 11750
07/17 - 10/17 12250
08/17 - 11/17 12750
09/17 - 12/17 13250
10/17 - 01/18 13750
11/17 - 02/18 14250
12/17 - 03/18 14750

回答

  1. 使用connect by level獲取十二個月資料
  2. 使用ADD_MONTHS將目前時間(sysdate)-(level+3)跟(level-1)取得連續四個月資料
  3. 使用group + avg 取得平均資料
with CTE as (
    select 
        --使用ADD_MONTHS-3跟-1取得連續四個月資料
        TO_DATE(TO_CHAR( ADD_MONTHS(sysdate ,- (level+3)),'YYYY/MM/')||'01','yyyy/mm/dd') sDate
        ,TO_DATE(TO_CHAR( ADD_MONTHS(sysdate ,- level),'YYYY/MM/')||'01','yyyy/mm/dd') eDate
        ,level 
    from dual 
    --使用`connect by level`獲取十二個月資料
    connect by level <=12 
)
,CTE2 as (
  select * 
  from CTE T1
  left join T T2 on cast(T2."Month" as date) between T1.sDate and T1.eDate
)
select TO_CHAR(sDate,'MM/YY') || ' - ' || TO_CHAR(eDate,'MM/YY') as "Consecutive Months"
  ,round(avg("Consumption")) as "Avg. of 4 Months Consumption"
from CTE2
group by sDate,eDate
order by eDate

得到結果

Consecutive Months Avg. of 4 Months Consumption
04/17 - 07/17 10750
05/17 - 08/17 11000
06/17 - 09/17 11500
07/17 - 10/17 11500
08/17 - 11/17 11250
09/17 - 12/17 11333
10/17 - 01/18 11000
11/17 - 02/18 12000
12/17 - 03/18 (null)
01/18 - 04/18 (null)
02/18 - 05/18 (null)
03/18 - 06/18 (null)

Test DDL:

CREATE TABLE T
    ("Month" timestamp, "Consumption" int)
;

INSERT ALL 
    INTO T ("Month", "Consumption")
         VALUES ('01-Apr-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-May-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Jun-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Jul-2017 12:00:00 AM', 13000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Aug-2017 12:00:00 AM', 11000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Sep-2017 12:00:00 AM', 12000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Oct-2017 12:00:00 AM', 10000)
    INTO T ("Month", "Consumption")
         VALUES ('01-Nov-2017 12:00:00 AM', 12000)
SELECT * FROM dual
;

線上測試連結

假如大大們有更好做法，都可以留言討論。


圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 則留言

1
一級屠豬士
iT邦新手 2 級 ‧ 2018-09-02 17:07:40 
create table ithelp180902 (
  id serial primary key
, cts timestamp not null
, consumption int not null  
);

insert into ithelp180902(cts, consumption) values
('01-Apr-2017 12:00:00 AM', 10000),
('01-May-2017 12:00:00 AM', 10000),
('01-Jun-2017 12:00:00 AM', 10000),
('01-Jul-2017 12:00:00 AM', 13000),
('01-Aug-2017 12:00:00 AM', 11000),
('01-Sep-2017 12:00:00 AM', 12000),
('01-Oct-2017 12:00:00 AM', 10000),
('01-Nov-2017 12:00:00 AM', 12000);

with t1 as (
select extract(YEAR FROM cts) cyear
     , extract(MONTH FROM cts) cmonth
     , sum(consumption) sum_consum
  from ithelp180902
 group by extract(YEAR FROM cts), extract(MONTH FROM cts)
), t2 as (
select *
     , round(avg(sum_consum) over(order by cyear, cmonth rows between current row and 3 following),0) avg_consum
  from t1
), t3 as (
select extract(YEAR FROM n) cyear
     , extract(MONTH FROM n) cmonth
     , to_char(n, 'YYYY-MM')
    || ' => ' 
    || to_char((n + interval '3 months'), 'YYYY-MM') date_range
  from generate_series('2017-04-01'::timestamp, '2018-03-01'::timestamp, '1 months') g(n)
)
select t3.date_range
     , t2.avg_consum
  from t2
  join t3
 using (cyear, cmonth)
;  


     date_range     | avg_consum 
--------------------+------------
 2017-04 => 2017-07 |      10750
 2017-05 => 2017-08 |      11000
 2017-06 => 2017-09 |      11500
 2017-07 => 2017-10 |      11500
 2017-08 => 2017-11 |      11250
 2017-09 => 2017-12 |      11333
 2017-10 => 2018-01 |      11000
 2017-11 => 2018-02 |      12000
(8 筆資料列)
暐翰 iT邦大師 1 級 ‧ 2018-09-03 09:41:49 檢舉

驚嘆時間~

一.真方便，PGSQL可以簡單使用generate_series指定一個範圍時間，並指定格式切分成多個row
oracle還要用模擬的方式
可以參考Oracle实现POSTGRESQL的generate_series功能 - CSDN博客

select extract(YEAR FROM n) cyear
     , extract(MONTH FROM n) cmonth
     , to_char(n, 'YYYY-MM')
    || ' => ' 
    || to_char((n + interval '3 months'), 'YYYY-MM') date_range
from generate_series('2017-04-01'::timestamp, '2018-03-01'::timestamp, '1 months') g(n)

二.運用windows function : rows between current row and 3 following 簡化SQL取得連續四個月資料

select *
     , round(avg(sum_consum) over(order by cyear, cmonth rows between current row and 3 following),0) avg_consum
from t1
登入發表回應
1
純真的人
iT邦大師 1 級 ‧ 2018-09-03 16:40:18

恩??子查詢呢??

oracle能用子查詢嗎@@?

declare @Tab table(
	Months date
	,Consumption int
)
insert into @Tab
values('2017/4/17',10000)
,('2017/5/17',10000)
,('2017/6/17',10000)
,('2017/7/17',13000)
,('2017/8/17',11000)
,('2017/9/17',12000)
,('2017/10/17',10000)
,('2017/11/17',12000)

select Months
,(
	select avg(b.Consumption)
	from @Tab as b
	where b.Months between a.Months and dateadd(m,3,a.Months)
) as Avg_Consumption
from @Tab as a

https://ithelp.ithome.com.tw/upload/images/20180903/20061369dJV0X5IxKn.png

第一次用oracle
用線上網址測試~好像可以@_@a

select "Month"
,(
	select avg("b"."Consumption")
	from T "b"
    where "b"."Month" between "a"."Month" and ADD_MONTHS("a"."Month",3)

) as Avg_Consumption
from T "a"

http://sqlfiddle.com/#!4/bba7f/42

暐翰 iT邦大師 1 級 ‧ 2018-09-03 18:36:07 檢舉

純真的人 大大
不好意思，我描述資料不好
因為他的日期不一定剛好隔一個月
是一個明細表格，所以要使用 (2017-04 => 2017-07)格式
而不是2017/11/17

純真的人 iT邦大師 1 級 ‧ 2018-09-03 18:49:04 檢舉

可以先轉換成只有年/月/1也是可以的~
只是會多幾句轉換SQL

declare @Tab table(
	Months date
	,Consumption int
)
insert into @Tab
values('2017/4/17',10000)
,('2017/5/17',10000)
,('2017/6/18',10000)
,('2017/7/17',13000)
,('2017/8/17',11000)
,('2017/9/19',12000)
,('2017/10/25',10000)
,('2017/11/17',12000)

select Months
,(
	select avg(b.Consumption)
	from @Tab as b
	where Convert(date,Convert(varchar,year(b.Months)) + '/' + Convert(varchar,month(b.Months)) + '/1') between Convert(date,Convert(varchar,year(a.Months)) + '/' + Convert(varchar,month(a.Months)) + '/1') and dateadd(m,3,Convert(date,Convert(varchar,year(a.Months)) + '/' + Convert(varchar,month(a.Months)) + '/1'))
) as Avg_Consumption
from @Tab as a

或者2012以上

select Months
,(
	select avg(b.Consumption)
	from @Tab as b
	where DATEFROMPARTS(year(b.Months),month(b.Months),1) 
	between DATEFROMPARTS(year(a.Months),month(a.Months),1) 
	and DATEFROMPARTS(year(a.Months),month(a.Months),1)
) as Avg_Consumption
from @Tab as a
登入發表回應

我要留言

立即登入留言
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22206 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙